/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */

package com.informet.ipd.modelo.dao;

import com.informet.ipd.modelo.vo.AdarVO;
import com.informet.ipd.modelo.vo.CearVO;
import com.informet.ipd.modelo.vo.CrecerConDeporteVO;
import com.informet.ipd.modelo.vo.ProgEstrVO;
import com.informet.ipd.modelo.vo.UbigeoVO;
import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;
import javax.swing.JOptionPane;

/**
 *
 * @author LAPTOP01
 */
public class AdarDao extends DBase{


  Connection conn = null;

    public AdarDao() {
        super();
    }
// Ini distrito
  public void  setComboDistrito(UbigeoVO bean, ResultSet rs) throws SQLException{
          bean.setNUM_CODDIST_PK(rs.getString("NUM_CODDIST_PK"));
          bean.setTX_NOMDIST(rs.getString("TX_NOMDIST"));

    }

    public List getComboDistrito(String id) throws SQLException {

        PreparedStatement pstmt = null;
        Statement stmt = null;
        ResultSet rs = null;
        ArrayList<UbigeoVO> lista = new ArrayList<UbigeoVO>(0);
        //lista=null;
        String sql = "";
        try {
            conn = DriverManager.getConnection(url, usuario, password);

            sql = "SELECT  NUM_CODDIST_PK,TX_NOMDIST FROM ME_DISTRITO WHERE NUM_CODPRV_PK=?";
            pstmt = conn.prepareStatement(sql);
            pstmt.setString(1, id);
            rs = pstmt.executeQuery();
            UbigeoVO bean = null;
            while (rs.next()) {
                bean = new UbigeoVO();
                setComboDistrito(bean, rs);
                lista.add(bean);
            }
            rs.close();
            pstmt.close();
        } finally {
            if (rs != null) {
                rs.close();
                rs = null;
            }
            if (stmt != null) {
                stmt.close();
                stmt = null;
            }
            if (conn != null) {
                conn.close();
                conn = null;
            }

        }
     //  JOptionPane.showMessageDialog(null, lista.size());
        return lista;
    }
// Fin distrito

    public void  setDisciplina(AdarVO bean, ResultSet rs) throws SQLException{
          bean.setNumCodDisc(Integer.parseInt(rs.getString("NUM_CODDISC_PK")));
          bean.setTxsDescDisc(rs.getString("TX_DESCDISC"));

    }

    public void  setListAdar(AdarVO bean, ResultSet rs) throws SQLException{
          
          bean.setCodAdar(rs.getString("NUM_CODADAR_PK"));
          bean.setDescEstadio(rs.getString("TX_NOMBREESTADIOCOLISEO"));
          bean.setAnio(rs.getString("NUM_ANIO"));
          bean.setAnioInicio(rs.getString("NUM_ANIOINIcio"));
          //ubigeo
          bean.setCodRegion(rs.getString("num_coddpto_pk"));
          bean.setCodProvincia(rs.getString("num_codprv_pk"));
          bean.setCodDistrito(rs.getString("num_coddist_pk"));

    }

    public List getListAdar(AdarVO bean,String codUser) throws SQLException {

        PreparedStatement pstmt = null;
        Statement stmt = null;
        ResultSet rs = null;
        ArrayList<AdarVO> lista = new ArrayList<AdarVO>(0);
        //lista=null;
        String sql = "";
        
        try {
            conn = DriverManager.getConnection(url, usuario, password);            
            stmt = conn.createStatement();
            sql = " select "+
                  "  *  "+
                  " from me_adar mc inner join me_distrito d on mc.num_coddist_pk=d.num_coddist_pk "+
                  "                 inner join me_provincia p on p.num_codprv_pk=d.num_codprv_pk "+
                  "                 inner join me_departamento de on de.num_coddpto_pk=p.num_coddpto_pk "+
                  "  where 1=1  "+                  
                  "            and num_anio=datepart(yy,getdate())+1 ";
                  

                  if((codUser.equals("PROFESOR") ))
                    sql=sql+"     and mc.num_coduser="+codUser;

                  if((!bean.getAnioInicio().equals("-1") ))
                        sql=sql+"     and mc.num_anioinicio="+bean.getAnioInicio();

                  if((!bean.getDescEstadio().equals("") ))
                        sql=sql+"     and mc.TX_NOMBREESTADIOCOLISEO like '%"+bean.getDescEstadio()+"%'";

                 if((!bean.getCodDistrito().equals("-1") ))
                        sql=sql+"     and mc.num_coddist_pk="+bean.getCodDistrito();
                 
            rs = stmt.executeQuery(sql);
            AdarVO bean1 = null;
            while (rs.next()) {
                bean1 = new AdarVO();
                setListAdar(bean1, rs);
                lista.add(bean1);
            }
           
        } finally {
            if (rs != null) {
                rs.close();
                rs = null;
            }
            if (stmt != null) {
                stmt.close();
                stmt = null;
            }
            if (conn != null) {
                conn.close();
                conn = null;
            }

        }
      // JOptionPane.showMessageDialog(null, lista.size());
        return lista;
    }
       

      public String spManAdar(AdarVO bean,String codUser) {
        String msj = "";
        //String codP=bean.getCodPrograma();
        try {
        	//codDistrito anioInicio anio txtEstadio tipoOper codAdar
            conn = DriverManager.getConnection(url, usuario, password);
            CallableStatement cs = conn.prepareCall("{call Man_UPDATE_ADAR(?,?,?,?,?,?,?)}");
            
            cs.setString(1, bean.getCodDistrito());
            cs.setString(2, bean.getAnioInicio());
            cs.setString(3, bean.getAnio());
            cs.setString(4, bean.getDescEstadio());
            cs.setString(5, bean.getTipoOper());
            cs.setString(6, bean.getCodAdar());
            cs.setString(7, bean.getCodUser());
            cs.execute();
        } catch (Exception e) {
            JOptionPane.showMessageDialog(null, e.toString());
            msj = e.toString();
            return msj;
        }
        return msj;
    }

      // Ini Alumno
      // Ini Alumno
      // Ini Alumno
      // Ini Alumno

       public void  setListAdarAlumno(AdarVO bean, ResultSet rs) throws SQLException{

          bean.setCodAdar(rs.getString("NUM_CODADAR_PK"));
          bean.setCodAlumno(rs.getString("NUM_CODALUMNODAR_PK"));
          bean.setNombre(rs.getString("TX_NOMBREALUMNO"));
          bean.setApePaterno(rs.getString("TX_APEPATERNO"));
          bean.setApeMaterno(rs.getString("TX_APEMATERNO"));
          bean.setFecNacimiento(rs.getString("dt_nacimiento"));
          bean.setCodSeguroMedico(rs.getString("NUM_CODSEGUROMEDICO"));

          bean.setCodNivel(rs.getString("NUM_CODNIVEL"));

          if(rs.getString("NUM_CODSEGUROMEDICO").equals("1"))
              bean.setDescSeguroMedico("Si");
          if(rs.getString("NUM_CODSEGUROMEDICO").equals("2"))
              bean.setDescSeguroMedico("No");
          //ubigeo

          bean.setCodTipoDeportista(rs.getString("NUM_CODTIPODEPORTISTA"));
          if(rs.getString("NUM_CODTIPODEPORTISTA").equals("1"))
              bean.setDescTipoDeportista("DP");
          if(rs.getString("NUM_CODTIPODEPORTISTA").equals("2"))
              bean.setDescTipoDeportista("DC");
          if(rs.getString("NUM_CODTIPODEPORTISTA").equals("3"))
              bean.setDescTipoDeportista("DCAN");

         // bean.setCodDisciplina(rs.getString("NUM_CODISCCEAR"));
          
          bean.setEdad(rs.getString("edad"));

          bean.setCodCausaRetiro(rs.getString("NUM_CODCAUSARET_PK"));
          bean.setCodDisciplina(rs.getString("NUM_CODDISCIPLINA"));

          bean.setDescCausaRet(rs.getString("descCausaRet"));

    }
      public List getListAlumno
      (
      String codAdar,String codUser,String nombre, String paterno,
      String materno,String tipoDeportista,String nivel,String disciplina, String role,
      String seguroMedico
      )
       throws SQLException {

        Statement stmt = null;
        ResultSet rs = null;
        ArrayList<AdarVO> lista = new ArrayList<AdarVO>(0);
        String sql = "";

        try {
            conn = DriverManager.getConnection(url, usuario, password);
            stmt = conn.createStatement();
            sql = " select "+
                  " datediff(yy,DT_FECNACIMIENTO,getdate())as edad, "+
                  " isnull(NUM_CODCAUSARET_PK,'') as NUM_CODCAUSARET_PK, "+

                  " isnull(NUM_CODSEGUROMEDICO,'') as NUM_CODSEGUROMEDICO, "+
                  " isnull(NUM_CODTIPODEPORTISTA,'') as NUM_CODTIPODEPORTISTA, "+
                  " isnull(NUM_CODNIVEL,'') as NUM_CODNIVEL, "+

                  " isnull(convert(varchar,DT_FECNACIMIENTO,103),'')as dt_nacimiento, "+

                  " isnull((select TX_DESCCAUSARET from me_causaretadar where NUM_CODCAUSARET_PK=a.NUM_CODCAUSARET_PK),'')as descCausaRet, "+

                  "  *  "+
                  " from me_adar mc inner join me_alumnoadar a on mc.NUM_CODADAR_PK=a.NUM_CODADAR_PK "+

                  "  where 1=1  "+

                  "   and mc.num_anio=datepart(yy,getdate())+1 "+
                  "   and mc.NUM_CODADAR_PK="+codAdar;

            if(role.equals("PROFESOR"))
                sql =sql+ " and mc.NUM_CODUSER="+codUser;

            if(!nombre.equals(""))
                sql =sql+ " and a.TX_NOMBREALUMNO like '%"+nombre+"%'";

            if(!paterno.equals(""))
                sql =sql+ " and a.TX_APEPATERNO like '%"+paterno+"%'";

            if(!materno.equals(""))
                sql =sql+ " and a.TX_APEMATERNO like '%"+materno+"%'";

            if(!tipoDeportista.equals("-1"))
                sql =sql+ " and a.NUM_CODTIPODEPORTISTA="+tipoDeportista;

            if(!seguroMedico.equals("-1"))
                sql =sql+ " and a.NUM_CODSEGUROMEDICO="+seguroMedico;

            if(!nivel.equals("-1"))
                sql =sql+ " and a.NUM_CODNIVEL= '"+nivel+"'";

            if(!disciplina.equals("-1"))
                sql =sql+ " and a.NUM_CODDISCIPLINA="+disciplina;

            rs = stmt.executeQuery(sql);
            AdarVO bean1 = null;
            while (rs.next()) {
                bean1 = new AdarVO();
                setListAdarAlumno(bean1, rs);
                lista.add(bean1);
            }

        } finally {
            if (rs != null) {
                rs.close();
                rs = null;
            }
            if (stmt != null) {
                stmt.close();
                stmt = null;
            }
            if (conn != null) {
                conn.close();
                conn = null;
            }

        }
      // JOptionPane.showMessageDialog(null, lista.size());
        return lista;
    }

//apePaterno,apeMaterno,nombre,codNivel,codDisciplina,flagcapa,codAdar,tipoOper,codAlumno,fecNacimiento,codSeguroMedico
//codTipoDeportista
      public String spManAlumno(AdarVO bean,String codUser) {
        String msj = "";
        //String codP=bean.getCodPrograma();
        try {
        	//codDistrito anioInicio anio txtEstadio tipoOper codAdar
            conn = DriverManager.getConnection(url, usuario, password);
            CallableStatement cs = conn.prepareCall("{call Man_UPDATE_ADARALUMNO(?,?,?,?,?,?,?,?,?,?,?)}");

            cs.setString(1, bean.getApePaterno());
            cs.setString(2, bean.getApeMaterno());
            cs.setString(3, bean.getNombre());
            cs.setString(4, bean.getCodNivel());
            cs.setString(5, bean.getCodDisciplina());
            cs.setString(6, bean.getCodAdar());

            cs.setString(7, bean.getTipoOper());
            cs.setString(8, bean.getCodAlumno());
            cs.setString(9, bean.getFecNacimiento());
            cs.setString(10, bean.getCodSeguroMedico());
            cs.setString(11, bean.getCodTipoDeportista());
            cs.execute();
        } catch (Exception e) {
            JOptionPane.showMessageDialog(null, e.toString());
            msj = e.toString();
            return msj;
        }
        return msj;
    }



//// Ini Marca Personal
//// Ini Marca Personal
//// Ini Marca Personal
//// Ini Marca Personal
//// Ini Marca Personal


    public void  setListMarcaPersonal(AdarVO bean, ResultSet rs) throws SQLException{
          bean.setDescModalidad(rs.getString("TX_DESCMODALIDAD"));
          bean.setDescCategoria(rs.getString("TX_DESCATEGORIA"));
          bean.setCodOlimpico(rs.getString("NUM_CODOLIMPICO"));
          if(bean.getCodOlimpico().equals("1"))
              bean.setDescOlimpico("Olimpicos");

          if(bean.getCodOlimpico().equals("2"))
              bean.setDescOlimpico("Panamericanos");

          if(bean.getCodOlimpico().equals("3"))
              bean.setDescOlimpico("Sudamericanos");

          bean.setCodFederado(rs.getString("NUM_CODFEDERADO"));
          if(bean.getCodFederado().equals("1"))
              bean.setDescFederado("Panamericano");
          
          if(bean.getCodFederado().equals("2"))
              bean.setDescFederado("Sudamericano");
          
          if(bean.getCodFederado().equals("3"))
              bean.setDescFederado("Bolivariano");

          bean.setCodModalidad(rs.getString("NUM_CODMODALIDAD"));
          
    }

     public List getlistMarcapersonal(String idPerfil,String codAlumno,String modalidad,String categoria,
             String codCircuitoOlimpico,String codCircuitoFederado) throws SQLException {

        Statement stmt = null;
        ResultSet rs = null;
        ArrayList<AdarVO> lista = new ArrayList<AdarVO>(0);
        //List lista=null;
        //lista=null;
        String sql = "";
        try {
            conn = DriverManager.getConnection(url, usuario, password);
            stmt = conn.createStatement();
            sql =   " select "+
                    " * "+
                    " from me_modalidadadar m inner join me_alumnoadar a on m.NUM_CODALUMNODAR_PK=a.NUM_CODALUMNODAR_PK "+
                    " where a.NUM_CODALUMNODAR_PK= "+codAlumno;
            if(idPerfil!=null)
                sql=sql+"  and num_codperfil_pk="+idPerfil;

            if(!modalidad.equals(""))
                sql=sql+"  and m.TX_DESCMODALIDAD like '%"+modalidad+"%'";

            if(!categoria.equals(""))
                sql=sql+"  and m.TX_DESCATEGORIA like '%"+categoria+"%'";

            if(!codCircuitoOlimpico.equals("-1"))
                sql=sql+"  and m.NUM_CODOLIMPICO="+codCircuitoOlimpico;

            if(!codCircuitoFederado.equals("-1"))
                sql=sql+"  and m.NUM_CODFEDERADO="+codCircuitoFederado;


            rs = stmt.executeQuery(sql);
            AdarVO bean = null;
            while (rs.next()) {
                bean = new AdarVO();
               setListMarcaPersonal(bean, rs);

               lista.add(bean);

            }

        } finally {
            if (rs != null) {
                rs.close();
                rs = null;
            }
            if (stmt != null) {
                stmt.close();
                stmt = null;
            }
            if (conn != null) {
                conn.close();
                conn = null;
            }

        }
        return lista;
    }


      public String spManMarcaPersonal(String txtModalidad,String txtCategoria,String codCircuitoOlimpico,
                                       String codCircuitoFederado,String tipoOper,String codAlumno,
                                       String codModalidad) {
        String msj = "";
        try {
            conn = DriverManager.getConnection(url, usuario, password);
            CallableStatement cs = conn.prepareCall("{call Man_UPDATE_ADARALUMARPER(?,?,?,?,?,?,?)}");
            cs.setString(1, txtModalidad);
            cs.setString(2, txtCategoria);
            cs.setString(3, codCircuitoOlimpico);
            cs.setString(4, codCircuitoFederado);
            cs.setString(5, codAlumno);
            cs.setString(6, tipoOper);
            cs.setString(7, codModalidad);
            
            cs.execute();
        } catch (Exception e) {
            JOptionPane.showMessageDialog(null, e.toString());
            msj = e.toString();
            return msj;
        }
        return msj;
    }
//Ini maraca por evento
// Ini marca por evento
// Ini marca por evento
//Ini maraca por evento
// Ini marca por evento
// Ini marca por evento


      public void  setListMarcaPorEvento(AdarVO bean, ResultSet rs) throws SQLException{
          bean.setCodEventoAdar(rs.getString("NUM_CODEVENTOADAR"));
          bean.setNumMarca(rs.getString("NUM_MARCA"));
          bean.setDescEvento(rs.getString("TX_DESCEVENTO"));
    }

     public List getlistMarcapersonalPorEvento(String idPerfil,String codAlumno,
                           String marca,String evento) throws SQLException {

        Statement stmt = null;
        ResultSet rs = null;
        ArrayList<AdarVO> lista = new ArrayList<AdarVO>(0);
        //List lista=null;
        //lista=null;
        String sql = "";
        try {
            conn = DriverManager.getConnection(url, usuario, password);
            stmt = conn.createStatement();
            sql =   " select "+
                    " * "+
                    " from me_EVENTOadar m inner join me_alumnoadar a on m.NUM_CODALUMNODAR_PK=a.NUM_CODALUMNODAR_PK "+
                    " where a.NUM_CODALUMNODAR_PK= "+codAlumno;
            if(idPerfil!=null)
                sql=sql+"  and num_codperfil_pk="+idPerfil;

            if(!marca.equals(""))
                sql=sql+"  and m.NUM_MARCA='"+marca+"'";

            if(!evento.equals(""))
                sql=sql+"  and m.TX_DESCEVENTO like '%"+evento+"%'";
            
            rs = stmt.executeQuery(sql);
            AdarVO bean = null;
            while (rs.next()) {
                bean = new AdarVO();
               setListMarcaPorEvento(bean, rs);
               lista.add(bean);
            }

        } finally {
            if (rs != null) {
                rs.close();
                rs = null;
            }
            if (stmt != null) {
                stmt.close();
                stmt = null;
            }
            if (conn != null) {
                conn.close();
                conn = null;
            }

        }
        return lista;
    }


      public String spManMarcaPersonalPorEvento(String codUser,String codAlumno,String descEvento,String descMarca,String tipoOper,String codEvento) {
        String msj = "";        
        try {

            conn = DriverManager.getConnection(url, usuario, password);
            CallableStatement cs = conn.prepareCall("{call Man_UPDATE_ADAREVENTOALUMARPER(?,?,?,?,?)}");
            cs.setString(1, descMarca);
            cs.setString(2, descEvento);
            cs.setString(3, tipoOper);
            cs.setString(4, codAlumno);
            cs.setString(5, codEvento);
            cs.execute();
        } catch (Exception e) {
            JOptionPane.showMessageDialog(null, e.toString());
            msj = e.toString();
            return msj;
        }
        return msj;
    }


////Ini Expectativas
////Ini Expectativas
////Ini Expectativas

    public void  setListExpectativa(AdarVO bean, ResultSet rs) throws SQLException{
          bean.setDescEvento(rs.getString("TX_EVENTO"));
           bean.setCodOlimpico(rs.getString("NUM_CODOLIMPICO"));

          bean.setDescOlimpico("");

          if(bean.getCodOlimpico().equals("1"))
              bean.setDescOlimpico("Olimpicos");

          if(bean.getCodOlimpico().equals("2"))
              bean.setDescOlimpico("Panamericanos");

          if(bean.getCodOlimpico().equals("3"))
              bean.setDescOlimpico("Sudamericanos");

          bean.setCodFederado(rs.getString("NUM_CODFEDERADO"));
           bean.setDescFederado("");
           
          if(bean.getCodFederado().equals("1"))
              bean.setDescFederado("Panamericano");

          if(bean.getCodFederado().equals("2"))
              bean.setDescFederado("Sudamericano");

          if(bean.getCodFederado().equals("3"))
              bean.setDescFederado("Bolivariano");
          
          bean.setCodMarca(rs.getString("NUM_MARCA"));         
          bean.setCodExpectativa(rs.getString("NUM_CODEXPECTATIVA_PK"));
          bean.setPuesto(rs.getString("NUM_RESULTADOEXP"));
          bean.setFechaIni(rs.getString("DT_FECINIEXP"));
          bean.setFechaFin(rs.getString("DT_FECFINEXP"));
    }

     public List getlistExpectativa(String idPerfil, String codUser,String codOlimpico,String codFederado,
                                    String marca,String resultado,String descEvento,String codAlumno,
                                    String fecIni,String fecFin)
                                    throws SQLException {

        Statement stmt = null;
        ResultSet rs = null;
        ArrayList<AdarVO> lista = new ArrayList<AdarVO>(0);
        String sql = "";
        try {
            conn = DriverManager.getConnection(url, usuario, password);
            stmt = conn.createStatement();
            sql =   " select " +
                    " isnull(convert(varchar,DT_FECINIEXP,103),'') as DT_FECINIEXP, "+
                    " isnull(convert(varchar,DT_FECFINEXP,103),'') as DT_FECFINEXP, "+
                    " isnull(TX_EVENTO,'') as TX_EVENTO, "+
                    " isnull(NUM_CODOLIMPICO,'') as NUM_CODOLIMPICO, "+
                    " isnull(NUM_CODFEDERADO,'') as NUM_CODFEDERADO, "+
                    " isnull(NUM_MARCA,'') as NUM_MARCA, "+
                    " isnull(NUM_CODEXPECTATIVA_PK,'') as NUM_CODEXPECTATIVA_PK, "+
                    " isnull(NUM_RESULTADOEXP,'') as NUM_RESULTADOEXP, "+

                    " * "+
                    " from me_expectativaadar e inner join me_alumnoadar a on a.NUM_CODALUMNODAR_PK=e.NUM_CODALUMNODAR_PK "+
                    " where a.NUM_CODALUMNODAR_PK="+codAlumno;

                    if(!descEvento.equals(""))
                        sql=sql+"           and e.TX_EVENTO like '%"+descEvento+"%'";

                    if(!codOlimpico.equals("-1"))
                        sql=sql+"           and e.NUM_CODOLIMPICO="+codOlimpico;

                    if(!codFederado.equals("-1"))
                        sql=sql+"           and e.NUM_CODFEDERADO="+codFederado;

                    if(!marca.equals(""))
                        sql=sql+"           and e.NUM_MARCA ="+marca;

                    if(!resultado.equals(""))
                        sql=sql+"           and e.NUM_RESULTADOEXP ="+resultado;

                    if(!fecIni.equals(""))
                        sql=sql+"  and convert(varchar,e.DT_FECINIEXP,103)>='"+fecIni+"'";

                    if(!fecFin.equals(""))
                        sql=sql+"  and convert(varchar,e.DT_FECFINEXP,103) <='"+fecFin+"'";

            if(idPerfil.equals("www"))
                sql=sql+"  and num_codperfil_pk="+idPerfil;

            sql=sql+" order by e.NUM_CODOLIMPICO desc";

            rs = stmt.executeQuery(sql);
            AdarVO bean = null;
            while (rs.next())
            {
                bean = new AdarVO();
               setListExpectativa(bean, rs);
               lista.add(bean);
            }

        } finally {
            if (rs != null) {
                rs.close();
                rs = null;
            }
            if (stmt != null) {
                stmt.close();
                stmt = null;
            }
            if (conn != null) {
                conn.close();
                conn = null;
            }

        }
        return lista;
    }


      public String spManE(String idPerfil, String codUser,String codOlimpico,String codFederado,
                                    String marca,String resultado,String descEvento,String codAlumno,
                                    String fecIni,String fecFin,String codExpectativa,
                                    String tipoOper) {
        String msj = "";
        try {

            conn = DriverManager.getConnection(url, usuario, password);
            CallableStatement cs = conn.prepareCall("{call Man_UPDATE_ADARALUMNOMEXPECTATIVA(?,?,?,?,?,?,?,?,?,?)}");
            cs.setString(1, descEvento);
            cs.setString(2, codOlimpico);
            cs.setString(3, codFederado);
            cs.setString(4, marca);
            cs.setString(5, resultado);
            cs.setString(6, codAlumno);
            cs.setString(7, fecIni);
            cs.setString(8, fecFin);
            cs.setString(9, codExpectativa);
            cs.setString(10,tipoOper);
            cs.execute();
        } catch (Exception e) {
            JOptionPane.showMessageDialog(null, e.toString());
            msj = e.toString();
            return msj;
        }
        return msj;
    }


      ///Ini Resultado
      ///Ini Resultado
      ///Ini Resultado
      ///Ini Resultado
      ///Ini Resultado
      ///Ini Resultado


        public void  setListResultados(AdarVO bean, ResultSet rs) throws SQLException{

             bean.setDescEvento(rs.getString("TX_EVENTO"));
           bean.setCodOlimpico(rs.getString("NUM_CODOLIMPICO"));

          bean.setDescOlimpico("");

          if(bean.getCodOlimpico().equals("1"))
              bean.setDescOlimpico("Olimpicos");

          if(bean.getCodOlimpico().equals("2"))
              bean.setDescOlimpico("Panamericanos");

          if(bean.getCodOlimpico().equals("3"))
              bean.setDescOlimpico("Sudamericanos");

          bean.setCodFederado(rs.getString("NUM_CODFEDERADO"));
           bean.setDescFederado("");

          if(bean.getCodFederado().equals("1"))
              bean.setDescFederado("Panamericano");

          if(bean.getCodFederado().equals("2"))
              bean.setDescFederado("Sudamericano");

          if(bean.getCodFederado().equals("3"))
              bean.setDescFederado("Bolivariano");

          bean.setCodMarca(rs.getString("NUM_MARCA"));
          bean.setCodExpectativa(rs.getString("NUM_CODEXPECTATIVA_PK"));
          bean.setPuesto(rs.getString("NUM_RESULTADOEXP"));
          bean.setFechaIni(rs.getString("DT_FECINIEXP"));
          bean.setFechaFin(rs.getString("DT_FECFINEXP"));

          //////////////Ini seteo de resultados
          bean.setNumMarcaRes(rs.getString("NUM_MARCARES"));
          bean.setNumLugar(rs.getString("NUM_LUGAR"));

          bean.setCodTrimestre(rs.getString("NUM_CODTRIMESTRE"));
          bean.setDescTrimestre("");
          if(bean.getCodTrimestre().equals("1"))
              bean.setDescTrimestre("Trimestre 1");

          if(bean.getCodTrimestre().equals("2"))
              bean.setDescTrimestre("Trimestre 2");

          if(bean.getCodTrimestre().equals("3"))
              bean.setDescTrimestre("Trimestre 3");

          if(bean.getCodTrimestre().equals("4"))
              bean.setDescTrimestre("Trimestre 4");

          bean.setNumDiferencial(rs.getString("NUM_DIFERENCIAL"));
          bean.setNumCumplimiento(rs.getString("NUM_CUMPLIMIENTO"));
          bean.setNumRankOlimpico(rs.getString("NUM_RANKOLIMPICO"));
          bean.setNumRankFederado(rs.getString("NUM_RANKFEDERADO"));


          bean.setDescCausaRet(rs.getString("descCausaRet"));


}

     public List getlistResultados(String idPerfil,String codAlumno,String descEvento,String descPrueba) throws SQLException {

        Statement stmt = null;
        ResultSet rs = null;
        ArrayList<AdarVO> lista = new ArrayList<AdarVO>(0);
        //List lista=null;
        //lista=null;
        String sql = "";
        try {
            conn = DriverManager.getConnection(url, usuario, password);
            stmt = conn.createStatement();
            sql =   " select " +

                   " isnull(convert(varchar,e.DT_FECINIEXP,103),'') as DT_FECINIEXP, "+
                    " isnull(convert(varchar,e.DT_FECFINEXP,103),'') as DT_FECFINEXP, "+
                    " isnull(e.TX_EVENTO,'') as TX_EVENTO, "+
                    " isnull(e.NUM_CODOLIMPICO,'') as NUM_CODOLIMPICO, "+
                    " isnull(e.NUM_CODFEDERADO,'') as NUM_CODFEDERADO, "+
                    " isnull(e.NUM_MARCA,'') as NUM_MARCA, "+
                    " isnull(e.NUM_CODEXPECTATIVA_PK,'') as NUM_CODEXPECTATIVA_PK, "+
                    " isnull(e.NUM_RESULTADOEXP,'') as NUM_RESULTADOEXP, "+
//Ini data resultados
                    " isnull(r.NUM_MARCARES,'') as NUM_MARCARES, "+
                    " isnull(r.NUM_LUGAR,'') as NUM_LUGAR, "+
                    " isnull(r.NUM_CODTRIMESTRE,'') as NUM_CODTRIMESTRE, "+
                    " isnull(r.NUM_DIFERENCIAL,'') as NUM_DIFERENCIAL, "+
                    " isnull(r.NUM_CUMPLIMIENTO,'') as NUM_CUMPLIMIENTO, "+
                    " isnull(r.NUM_RANKOLIMPICO,'') as NUM_RANKOLIMPICO, "+
                    " isnull(r.NUM_RANKFEDERADO,'') as NUM_RANKFEDERADO, "+

                    " isnull((select TX_DESCCAUSARET from me_causaretadar where NUM_CODCAUSARET_PK=a.NUM_CODCAUSARET_PK),'')as descCausaRet, "+


                    " * " +
                    " from  me_expectativaadar e left outer join me_resultadoadar r on e.NUM_CODEXPECTATIVA_PK=r.NUM_CODEXPECTATIVA_PK "+
						      " inner join me_alumnoadar a on a.NUM_CODALUMNODAR_PK=e.NUM_CODALUMNODAR_PK "+
                    " where a.NUM_CODALUMNODAR_PK= "+codAlumno;


                    if(!descEvento.equals(""))
                        sql=sql+"           and e.TX_EVENTO like '%"+descEvento+"%'";

                    if(!descPrueba.equals(""))
                        sql=sql+"           and e.TX_PRUEBA like '%"+descPrueba+"%'";


            if(idPerfil!=null)
                sql=sql+"  and num_codperfil_pk="+idPerfil;

            sql=sql+" order by e.NUM_CODOLIMPICO desc ";




            rs = stmt.executeQuery(sql);
            AdarVO bean = null;
            while (rs.next()) {
                bean = new AdarVO();
               setListResultados(bean, rs);
               lista.add(bean);
            }

        } finally {
            if (rs != null) {
                rs.close();
                rs = null;
            }
            if (stmt != null) {
                stmt.close();
                stmt = null;
            }
            if (conn != null) {
                conn.close();
                conn = null;
            }

        }
        return lista;
    }

      public String spManR(String idPerfil,String codUser,String tipoOper,
                           String codAlumno,String codExpectativa,String txtMarca,String txtLugar,
                           String rankOlimpico,String rankFederado,String codRetiro,String codTrimestre
                           ) {
        String msj = "";
        try {
            conn = DriverManager.getConnection(url, usuario, password);//Man_UPDATE_CEARALUMNOMONITOREO
            CallableStatement cs = conn.prepareCall("{call Man_UPDATE_ADARALUMNORESULTADO(?,?,?,?,?,?,?,?,?)}");
            cs.setString(1, tipoOper );
            cs.setString(2, txtLugar);
            cs.setString(3, codExpectativa);
            cs.setString(4, rankOlimpico);
            cs.setString(5, rankFederado);
            cs.setString(6, codRetiro);
            cs.setString(7, codTrimestre);
            cs.setString(8, txtMarca);
            cs.setString(9, codAlumno);
            cs.execute();
        } catch (Exception e) {
            JOptionPane.showMessageDialog(null, e.toString());
            msj = e.toString();
            return msj;
        }
        return msj;
    }

////// Ini agregado para las permanencias

    public void  setCausaRetiro(AdarVO bean, ResultSet rs) throws SQLException{

           bean.setCodEtiqueta(rs.getString("NUM_CODcausaret_PK"));
           bean.setDescEtiqueta(rs.getString("tx_desccausaret"));
         //bean.setCodcausaRet(rs.getString("NUM_CODcausaret_PK"));
         //  bean.setDescCausaRet(rs.getString("tx_desccausaret"));


    }

    public List getListCausaRetiro(String id,String trimestre)  throws SQLException {
        Statement stmt = null;
        ResultSet rs = null;
        ArrayList<AdarVO> lista = new ArrayList<AdarVO>(0);

        String sql = "";
        try {
            conn = DriverManager.getConnection(url, usuario, password);
            stmt = conn.createStatement();

                sql = "  select * from me_causaretadar ";

            rs = stmt.executeQuery(sql);
            AdarVO bean = null;
            while (rs.next()) {
                bean = new AdarVO();
                setCausaRetiro(bean,rs);
                lista.add(bean);
            }

        } finally {
            if (rs != null) {
                rs.close();
                rs = null;
            }
            if (stmt != null) {
                stmt.close();
                stmt = null;
            }
            if (conn != null) {
                conn.close();
                conn = null;
            }

        }
        return lista;
    }

     public String spManCausaRetiro(String codEtiqueta,String nombEtiqueta,String tipoOper) {
        String msj = "";
        //String codP=bean.getCodPrograma();
        try {

            conn = DriverManager.getConnection(url, usuario, password);
            CallableStatement cs = conn.prepareCall("{call Man_update_MeCausaRetAdar(?,?,?)}");
            //codActividad, codAnio, titu_programa,justificacion,fechaInicio, fechaFin,monto
            cs.setString(1, codEtiqueta);
            cs.setString(2, nombEtiqueta);
            cs.setString(3, tipoOper);
            cs.execute();
        } catch (Exception e) {
            JOptionPane.showMessageDialog(null, e.toString());
            msj = e.toString();
            return msj;
        }
        return msj;
    }
/////////
     public String spManPermanencia(String codAlumno,String codEtiqueta) {
        String msj = "";
        //String codP=bean.getCodPrograma();
        try {

            conn = DriverManager.getConnection(url, usuario, password);
            CallableStatement cs = conn.prepareCall("{call Man_UPDATE_ADARALUMNOCAUSARETIRO(?,?)}");
            //codActividad, codAnio, titu_programa,justificacion,fechaInicio, fechaFin,monto
            cs.setString(1, codAlumno);
            cs.setString(2, codEtiqueta);
            cs.execute();
        } catch (Exception e) {
            JOptionPane.showMessageDialog(null, e.toString());
            msj = e.toString();
            return msj;
        }
        return msj;
    }


}
